package edu.uic.ids517;

import java.io.BufferedReader;
import java.io.BufferedWriter;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStreamReader;
import java.io.OutputStreamWriter;
import java.io.Writer;
import java.nio.file.LinkOption;
import java.nio.file.Path;
import java.nio.file.Paths;
import java.sql.Date;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Types;
import java.util.ArrayList;
import java.util.Map;
import java.util.Scanner;

import javax.faces.bean.ManagedBean;
import javax.faces.bean.SessionScoped;
import javax.faces.context.ExternalContext;
import javax.faces.context.FacesContext;

import org.apache.myfaces.custom.fileupload.UploadedFile;

@ManagedBean
@SessionScoped
public class ActionBeanFile {

	UploadedFile uploadedFile;
	String fileLabel;

	boolean fileImport;
	int numberRows;

	boolean fileImportError;
	MessageBean messageBean;
	String message;
	UserLoginBean userLoginBean;
	boolean renderParseTable;

	String fileName;
	long fileSize;
	String fileContentType;

	ArrayList<DataBean> dataBean;
	ActionBeanDbaseAccess dbaseBean;
	TableListBean tableListBean;

	String importPath;

	public TableListBean getTableListBean() {
		return tableListBean;
	}

	public void setTableListBean(TableListBean tableListBean) {
		this.tableListBean = tableListBean;
	}

	public String getFileLabel() {
		return fileLabel;
	}

	public void setFileLabel(String fileLabel) {
		this.fileLabel = userLoginBean.getLoginId() + "_" + fileLabel;
	}

	public boolean isFileImport() {
		return fileImport;
	}

	public void setFileImport(boolean fileImport) {
		this.fileImport = fileImport;
	}

	public boolean isFileImportError() {
		return fileImportError;
	}

	public void setFileImportError(boolean fileImportError) {
		this.fileImportError = fileImportError;
	}

	public UploadedFile getUploadedFile() {
		return uploadedFile;
	}

	public void setUploadedFile(UploadedFile uploadedFile) {
		this.uploadedFile = uploadedFile;
	}


	public String getMessage() {
		return message;
	}

	public void setMessage(String message) {
		this.message = message;
	}

	public MessageBean getMessageBean() {
		return messageBean;
	}

	public void setMessageBean(MessageBean messageBean) {
		this.messageBean = messageBean;
	}

	public ActionBeanDbaseAccess getDbaseBean() {
		return dbaseBean;
	}

	public void setDbaseBean(ActionBeanDbaseAccess dbaseBean) {
		this.dbaseBean = dbaseBean;
	}

	public ArrayList<DataBean> getDataBean() {
		return dataBean;
	}

	public void setDataBean(ArrayList<DataBean> dataBean) {
		this.dataBean = dataBean;
	}

	public int getNumberRows() {
		return numberRows;
	}

	public void setNumberRows(int numberRows) {
		this.numberRows = numberRows;
	}

	public boolean isRenderParseTable() {
		return renderParseTable;
	}

	public UserLoginBean getUserLoginBean() {
		return userLoginBean;
	}

	public void setUserLoginBean(UserLoginBean userLoginBean) {
		this.userLoginBean = userLoginBean;
	}

	public String processFileUpload() {

		System.out.println("inside processFileUpload");

		//String uploadedFileContents = null;
		messageBean.setMessage("");

		FacesContext context = FacesContext.getCurrentInstance();
		Map <String, Object> z = context.getExternalContext().getSessionMap();

		String path = context.getExternalContext().getRealPath("/WebContent/temp");
		System.out.println("path: " + path);

		File tempFile = null;
		FileOutputStream fos = null;

		int nCols = 0;
		int n = 0;

		fileImport = false;
		fileImportError = true;
		fileName = null;

		try {
			fileName = uploadedFile.getName();
			fileSize = uploadedFile.getSize();
			fileContentType = uploadedFile.getContentType();

			System.out.println("fileName: " + fileName + "\n fileSize :" + fileSize + "\n fileContentType: " + fileContentType);

			// next line if want upload in String for memory processing
			//uploadedFileContents = new String(uploadedFile.getBytes());

			FacesContext facesContext = FacesContext.getCurrentInstance();
			ExternalContext externalContext = facesContext.getExternalContext();
			importPath = facesContext.getExternalContext().getRealPath("/tempExport");
			System.out.println("Path is : " + importPath);

			String newFilePath = importPath + "\\" +fileLabel+".txt";

			System.out.println("Path is : " + newFilePath);


			tempFile = new File(newFilePath);
			System.out.println("Inside newFilePath---"+newFilePath);
			fos = new FileOutputStream(tempFile);
			if (!tempFile.exists()) {
				tempFile.createNewFile();
			}
			byte[] contentInBytes = uploadedFile.getBytes();

			fos.write(contentInBytes);
			fos.flush();
			fos.close();

			Scanner s = new Scanner(tempFile);
			String input;
			Scanner d;

			input = s.nextLine();
			System.out.println("Inside input---"+input);
			String[] strArr=input.split(",");
			//System.out.println(input);

			nCols = strArr.length;
			dataBean = new ArrayList<DataBean>(nCols);
			// variables - header line
			for(int i=0; i<nCols; i++) {
				//System.out.println("Inside 2222222222222222222222222222");
				dataBean.add(new DataBean());
				dataBean.get(i).setVariableName(strArr[i]);
				System.out.print(":" + strArr[i]);
			}


			while(s.hasNext()) {
				//System.out.println("Inside 33333333333333333");
				input = s.nextLine();
				strArr=input.split(",");
				System.out.println(input);
				System.out.println("After printing input" + strArr);
				nCols = strArr.length;

				for(int i=0; i<nCols; i++) {
					System.out.print(":" + strArr[i]);
					if(n==0) {
						String dataType = "VARCHAR(45)";
						String variableType = "CATEGORICAL";

						try {
							Date dParse = Date.valueOf(strArr[i]);
							dataType = "DATE";
							variableType = "QUANTITATIVE";
						}
						catch (Exception e) {
							dataType = "VARCHAR(45)";
							variableType = "CATEGORICAL";
						}
						if(!dataType.equalsIgnoreCase("date"))
							try {
								Integer.parseInt(strArr[i]);
								dataType = "INTEGER";
								variableType = "COUNT";
							}
						catch (NumberFormatException e) {
							dataType = "VARCHAR(45)";
							variableType = "CATEGORICAL";
						}

						if((!dataType.equalsIgnoreCase("integer")) &&
								(!dataType.equalsIgnoreCase("date")))
							try {
								Double.parseDouble(strArr[i]); //NumberFormatExceptiontype = "DOUBLE";
								variableType = "QUANTITATIVE";
							}
						catch (NumberFormatException e) {
							dataType = "VARCHAR(45)";
							variableType = "CATEGORICAL";
						}

						dataBean.get(i).setDataType(dataType);
						dataBean.get(i).setDataValue(strArr[i]);
						dataBean.get(i).setVariableType(variableType);
					}
				}
				System.out.println(":");
				d = new Scanner(input);
				d.useDelimiter(",");
				n++;
			}
			s.close();
			numberRows = n;
			fileImport = true;

		} // end try
		catch (IOException e) {
			e.printStackTrace();
			messageBean.setErrorMessage(true);
			messageBean.setMessage("Error reading data from file " + fileName + ", label: " + fileLabel);
			fileImportError = true;
		} // end catch

		numberRows = n;
		renderParseTable = true;
		return "SUCCESS";
	}

	public String processDataImport() throws IOException, SQLException {
		System.out.println("inside processFileUpload()");

		// Create table query
		StringBuffer sb = new StringBuffer("CREATE TABLE " + fileLabel + "(" );
		int nCols = (dataBean == null) ? 0 : dataBean.size();

		for(int i=0; i<nCols; i++) {
			sb.append(dataBean.get(i).getVariableName() + " " +
					dataBean.get(i).getDataType());
			if(i != (nCols-1))
				sb.append(", ");
		}//for ends

		sb.append(" )");

		System.out.println("Query: " +sb.toString());
		// Set query type
		dbaseBean.setQueryType("CREATE");

		// Get Database Connection
		dbaseBean.openConnection();

		// Execute the Create query
		if(!dbaseBean.executeSQL(sb.toString())){
			System.out.println(messageBean.getMessage());
			messageBean.setErrorMessage(true);
			fileLabel = "";
			return "FAIL";
		}
		/********************************** INSERT IN TO THE TABLE CREATED**************************************/

		// Open the file
		FileInputStream fstream = new FileInputStream(importPath + "\\" +fileLabel+".txt");
		BufferedReader br = new BufferedReader(new InputStreamReader(fstream));
		String strLine;
		ArrayList<String> strArray = new ArrayList<String>();

		//Read File Line By Line
		while ((strLine = br.readLine()) != null)   {
			// Print the content on the console
			strArray.add(strLine);
		}

		//Close the input stream
		br.close();


		// Create Insert Query
		System.out.println("logic for INSERT starts");
		dbaseBean.openConnection();		
		int nRows1 = strArray.size();
		for(int j =1; j<nRows1; j++)
		{
			StringBuffer sbInsert = new StringBuffer("INSERT into " + fileLabel + "(" );
			int nCols1 = (dataBean == null) ? 0 : dataBean.size();

			for(int i=0; i<nCols1; i++) {
				sbInsert.append(dataBean.get(i).getVariableName());
				if(i != (nCols1-1))
					sbInsert.append(", ");
			}
			sbInsert.append(") VALUES(");

			String[] strArrayElement;
			String strElement = strArray.get(j);
			strArrayElement = strElement.split(",");

			for(int i=0; i<nCols1; i++) {

				if(dataBean.get(i).getDataType().equalsIgnoreCase("VARCHAR(45)")){
					sbInsert.append("'");
				}

				String value = strArrayElement[i];
				sbInsert.append(value);


				if(dataBean.get(i).getDataType().equalsIgnoreCase("VARCHAR(45)")){
					sbInsert.append("'");
				}
				if(i != (nCols1-1))
					sbInsert.append(", ");
			}
			sbInsert.append(")");

			System.out.println("logic for INSERT ends");
			System.out.println(sbInsert.toString());


			// Set query type
			dbaseBean.setQueryType("INSERT");

			// Execute the Create query
			dbaseBean.executeSQL(sbInsert.toString());
		}

		dbaseBean.closeConnection();





		//*********************************************Testing***************************************************************
		/*		StringBuffer query = null;
		int nCols1 = (dataBean == null) ? 0 : dataBean.size();
		System.out.println("nCols1");
		for(int i=0; i<nCols1; i++){
		query = new StringBuffer("INSERT into variableTypeTable VALUES ('");
		query.append(fileLabel + "', '");
		query.append(dataBean.get(i).getVariableName() + "', '");
		query.append(dataBean.get(i).getVariableType() + "')");
		System.out.println("Query: " + query.toString());		

		dbaseBean.setQueryType("INSERT");
		dbaseBean.openConnection();
		dbaseBean.executeSQL(query.toString());
		}*/





		messageBean.setErrorMessage(true);
		messageBean.setMessage("Table " + fileLabel + " Imported");
		return "SUCCESS";
	}

	public String processDataExport() throws IOException, SQLException {

		String selectedTableName = tableListBean.getTableName();
		System.out.print("TABLE SELECTED----------"+selectedTableName);


		StringBuffer sbSelect = new StringBuffer("SELECT * FROM " + selectedTableName + "" );

		// Set query type
		dbaseBean.setQueryType("SELECT");

		// Get Database Connection
		dbaseBean.openConnection();

		// Execute the Create query
		ResultSet results =	dbaseBean.executeSelectSQL(sbSelect.toString());

		// Getting the column names
		ArrayList<String> columnName = new ArrayList<String>();
		ResultSetMetaData rsmd = null;
		rsmd = results.getMetaData();
		int columnCount = rsmd.getColumnCount();
		System.out.println("TABLE SELECTED-----columnCount-----"+columnCount);
		ArrayList<String> finalArray = new ArrayList<String>();
		for (int i=1; i<=columnCount; i++) {

			columnName.add(rsmd.getColumnName(i));
		}

		// Creating data of table column
		if (results != null) {
			while (results.next()) 
			{	
				StringBuffer resultArray = new StringBuffer();
				for (int i=1; i<=columnCount; i++) {
					int type = rsmd.getColumnType(i);
					if (type == Types.VARCHAR || type == Types.CHAR) {
						resultArray.append(results.getString(i));

					} else {
						resultArray.append(Integer.toString(results.getInt(i)));

					}
					if(i != columnCount){
						resultArray.append(",");
					}

				}
				//System.out.println("TABLE ROW DATA----------"+resultArray.toString());
				finalArray.add(resultArray.toString());
			}
		}


		// Creating a new file


		// Get the currently selected file from the editor
		FacesContext facesContext = FacesContext.getCurrentInstance();
		ExternalContext externalContext = facesContext.getExternalContext();
		String path = facesContext.getExternalContext().getRealPath("/tempExport");
		System.out.println("Path is : " + path);

		File newFileTxt = new File(path + "\\" +selectedTableName + ".csv" );
		FileOutputStream newFileFOS = new FileOutputStream(newFileTxt);
		OutputStreamWriter newFileOSW = new OutputStreamWriter(newFileFOS);    
		Writer newFileWriter = new BufferedWriter(newFileOSW);

		// writing column
		for(int a =0; a<columnName.size(); a++)
		{
			newFileWriter.write(columnName.get(a));
			if(a != (columnName.size()-1)){
				newFileWriter.write(",");
			}
		}

		// Writing row data
		for(int b =0; b<finalArray.size(); b++)
		{
			newFileWriter.write(System.lineSeparator());
			String element = finalArray.get(b);		
			newFileWriter.write(element);
		}
		newFileWriter.close();
		messageBean.setErrorMessage(true);
		messageBean.setMessage("Table Exported to tempExport folder: \n" + newFileTxt);

		return "SUCCESS";
	}
}
